{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af62cbc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0887e2f4",
   "metadata": {},
   "source": [
    "### 读取Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec87010f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"本月份上衣销量数据.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68ba6ce2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d17ef88",
   "metadata": {},
   "source": [
    "### 增加移动列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "976111e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"下一日\"] = df[\"销量\"].shift()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfed438d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eb0515b",
   "metadata": {},
   "source": [
    "### 计算日环比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44c5ffeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"日同比\"] = (df[\"销量\"] - df[\"下一日\"])*100 / df[\"下一日\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56ea42a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6214e529",
   "metadata": {},
   "source": [
    "### 清理临时字段，输出结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "040a0742",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns=[\"下一日\"], inplace=True)\n",
    "df.fillna(0.0, inplace=True)\n",
    "df[\"日同比\"] = df[\"日同比\"].map(lambda x : round(x, 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff6dc752",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d281c687",
   "metadata": {},
   "outputs": [],
   "source": [
    "file = '结果文件.xlsx'\n",
    "with pd.ExcelWriter(file, datetime_format='YYYY-MM-DD') as writer:\n",
    "    df.to_excel(writer, sheet_name='日同比数据', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
